package com.example.demo.ykj.rainfall.repository;

import com.example.demo.util.BaseRepository;
import com.example.demo.ykj.rainfall.entity.Rainfall;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface RainfallRepository extends BaseRepository<Rainfall, String> {

    //精确到时 年月日时
    @Query(value = "SELECT sum(yl) total, DATE_FORMAT(time,'%Y%m%d%H') day  FROM `rainfall` GROUP BY DATE_FORMAT(time,'%Y%m%d%H') ORDER BY total desc", nativeQuery = true)
    List<Map<String, Object>> getHour();

    //精确到日 年月日
    @Query(value = "SELECT sum(yl) total, DATE_FORMAT(time,'%Y%m%d') day  FROM `rainfall` GROUP BY DATE_FORMAT(time,'%Y%m%d') ORDER BY total desc", nativeQuery = true)
    List<Map<String, Object>> getDay();


    //清空数据库sql
//    DROP TABLE IF EXISTS `rainfall`;
//    CREATE TABLE `rainfall`  (
//            `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
//  `swbm` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '水文编码',
//            `lx` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '类型',
//            `time` datetime NULL DEFAULT NULL COMMENT '时间',
//            `bm` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '编码',
//            `bmxs` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '编码*系数',
//            `yl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '雨量',
//    PRIMARY KEY (`id`) USING BTREE
//) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
//    SET FOREIGN_KEY_CHECKS = 1;

}